package mysqlUtility

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
	"serialServer/utility"
	"fmt"
)

var DBConn * sql.DB
func ConnectToDB(config utility.MysqlConfig) {
	dataSoure := config.Username + ":" + config.Password + "@tcp(" + config.IP + ":" + config.Port + ")/" + config.DbName
	fmt.Println(dataSoure)
	var err error
	DBConn, err = sql.Open("mysql", dataSoure)
	if err != nil {
		panic(err.Error())
	}
	//DBConn.
}

func InsertIntoThermocouple(record utility.Thermocouple) bool {
	sqlStr := "INSERT INTO thermocouple (`uuid`, `deviceId`, `value`) VALUES (?, ?, ?)"
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	_, err = stmt.Exec(record.Uuid, record.DeviceId, record.Value)
	if err != nil {
		panic(err.Error())
	}
	stmt.Close()
	return true
}

func InsertIntoAccelerometer(record utility.Accelerometer) bool {
	sqlStr := "INSERT INTO accelerometer (`uuid`, `deviceId`, `acceRx`, `acceRy`, `acceRz`) VALUES (?, ?, ?, ?, ?)"
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	_, err = stmt.Exec(record.Uuid, record.DeviceId, record.AcceRx, record.AcceRy, record.AcceRz)
	if err != nil {
		panic(err.Error())
	}
	stmt.Close()
	return true
}


func InsertIntoGyroscope(record utility.Gyroscope) bool {
	sqlStr := "INSERT INTO gyroscope (`uuid`, `deviceId`, `gyroRx`, `gyroRy`, `gyroRz`) VALUES (?, ?, ?, ?, ?)"
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	_, err = stmt.Exec(record.Uuid, record.DeviceId, record.GyroRx, record.GyroRy, record.GyroRz)
	if err != nil {
		panic(err.Error())
	}
	stmt.Close()
	return true
}

func InsertIntoMagnetic(record utility.Magnetic) bool {
	sqlStr := "INSERT INTO magnetic (`uuid`, `deviceId`, `megnX`, `megnY`, `megnZ`) VALUES (?, ?, ?, ?, ?)"
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	_, err = stmt.Exec(record.Uuid, record.DeviceId, record.MegnX, record.MegnY, record.MegnZ)
	if err != nil {
		panic(err.Error())
	}
	stmt.Close()
	return true
}

func InsertIntoBarometer(record utility.Barometer) bool {
	sqlStr := "INSERT INTO barometer (`uuid`, `deviceId`, `value`) VALUES (?, ?, ?)"
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	_, err = stmt.Exec(record.Uuid, record.DeviceId, record.Value)
	if err != nil {
		panic(err.Error())
	}
	stmt.Close()
	return true
}

func InsertIntoElectricResistance(record utility.ElectricResistance) bool {
	sqlStr := "INSERT INTO electricResistance (`uuid`, `deviceId`, `value`) VALUES (?, ?, ?)"
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	_, err = stmt.Exec(record.Uuid, record.DeviceId, record.Value)
	if err != nil {
		panic(err.Error())
	}
	stmt.Close()
	return true
}

func GetLastestThermocoupleByDeviceId(deviceId string) utility.Thermocouple {
	sqlStr := "SELECT `uuid`, `deviceId`, `value`, `insertDatetime` FROM thermocouple WHERE `deviceId`=? ORDER BY `insertDatetime` DESC LIMIT 1;"
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	rows, err := stmt.Query(deviceId)
	if err != nil {
		panic(err.Error())
	}
	var record utility.Thermocouple
	if rows.Next() {
		rows.Scan(&record.Uuid, &record.DeviceId, &record.Value, &record.InsertDatetime)
	}
	rows.Close()
	stmt.Close()
	return record
}

func GetLastestAccelerometerByDeviceId(deviceId string) utility.Accelerometer {
	sqlStr := "SELECT `uuid`, `deviceId`, `acceRx`, `acceRy`, `acceRz`, `insertDatetime` FROM accelerometer WHERE `deviceId`=? ORDER BY `insertDatetime` DESC LIMIT 1;"
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	rows, err := stmt.Query(deviceId)
	if err != nil {
		panic(err.Error())
	}
	var record utility.Accelerometer
	if rows.Next() {
		rows.Scan(&record.Uuid, &record.DeviceId, &record.AcceRx, &record.AcceRy, &record.AcceRz, &record.InsertDatetime)
	}
	rows.Close()
	stmt.Close()
	return record
}

func GetLastestGyroscopeByDeviceId(deviceId string) utility.Gyroscope {
	sqlStr := "SELECT `uuid`, `deviceId`, `gyroRx`, `gyroRy`, `gyroRz`, `insertDatetime` FROM gyroscope WHERE `deviceId`=? ORDER BY `insertDatetime` DESC LIMIT 1;"
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	rows, err := stmt.Query(deviceId)
	if err != nil {
		panic(err.Error())
	}
	var record utility.Gyroscope
	if rows.Next() {
		rows.Scan(&record.Uuid, &record.DeviceId, &record.GyroRx, &record.GyroRy, &record.GyroRz, &record.InsertDatetime)
	}
	rows.Close()
	stmt.Close()
	return record
}

func GetLastestMagneticByDeviceId(deviceId string) utility.Magnetic {
	sqlStr := "SELECT `uuid`, `deviceId`, `megnX`, `megnY`, `megnZ`, `insertDatetime` FROM magnetic WHERE `deviceId`=? ORDER BY `insertDatetime` DESC LIMIT 1;"
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	rows, err := stmt.Query(deviceId)
	if err != nil {
		panic(err.Error())
	}
	var record utility.Magnetic
	if rows.Next() {
		rows.Scan(&record.Uuid, &record.DeviceId, &record.MegnX, &record.MegnY, &record.MegnZ, &record.InsertDatetime)
	}
	rows.Close()
	stmt.Close()
	return record
}

func GetLastestBarometerByDeviceId(deviceId string) utility.Barometer {
	sqlStr := "SELECT `uuid`, `deviceId`, `value`, `insertDatetime` FROM barometer WHERE `deviceId`=? ORDER BY `insertDatetime` DESC LIMIT 1;"
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	rows, err := stmt.Query(deviceId)
	if err != nil {
		panic(err.Error())
	}
	var record utility.Barometer
	if rows.Next() {
		rows.Scan(&record.Uuid, &record.DeviceId, &record.Value, &record.InsertDatetime)
	}
	rows.Close()
	stmt.Close()
	return record
}

func GetLastestElectricResistanceByDeviceId(deviceId string) utility.ElectricResistance {
	sqlStr := "SELECT `uuid`, `deviceId`, `value`, `insertDatetime` FROM electricResistance WHERE `deviceId`=? ORDER BY `insertDatetime` DESC LIMIT 1;"
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	rows, err := stmt.Query(deviceId)
	if err != nil {
		panic(err.Error())
	}
	var record utility.ElectricResistance
	if rows.Next() {
		rows.Scan(&record.Uuid, &record.DeviceId, &record.Value, &record.InsertDatetime)
	}
	rows.Close()
	stmt.Close()
	return record
}

func GetRecentThermocoupleByDeviceIdAndCount(deviceId string, startDatetime string, count int32) []utility.Thermocouple {
	if count <= 0 {
		count = 100
	}
	var sqlStr string
	if startDatetime == "" {
		sqlStr = "SELECT `uuid`, `deviceId`, `value`, `insertDatetime` FROM thermocouple WHERE `deviceId`=? ORDER BY `insertDatetime`  LIMIT ?;"
	} else {
		sqlStr = "SELECT `uuid`, `deviceId`, `value`, `insertDatetime` FROM thermocouple WHERE `deviceId`=? AND `insertDatetime` > ? ORDER BY `insertDatetime`  LIMIT ?;"
	}
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	var rows * sql.Rows
	if startDatetime == "" {
		rows, err = stmt.Query(deviceId, count)
	} else {
		rows, err = stmt.Query(deviceId, startDatetime, count)
	}
	if err != nil {
		panic(err.Error())
	}
	var recordList []utility.Thermocouple
	for rows.Next() {
		var record utility.Thermocouple
		rows.Scan(&record.Uuid, &record.DeviceId, &record.Value, &record.InsertDatetime)
		recordList = append(recordList, record)
	}
	rows.Close()
	stmt.Close()
	return recordList
}

func GetRecentAccelerometerByDeviceIdAndCount(deviceId string, startDatetime string, count int32) []utility.Accelerometer {
	if count <= 0 {
		count = 100
	}
	var sqlStr string
	if startDatetime == "" {
		sqlStr = "SELECT `uuid`, `deviceId`, `acceRx`, `acceRy`, `acceRz`, `insertDatetime` FROM accelerometer WHERE `deviceId`=? ORDER BY `insertDatetime`  LIMIT ?;"
	} else {
		sqlStr = "SELECT `uuid`, `deviceId`, `acceRx`, `acceRy`, `acceRz`, `insertDatetime` FROM accelerometer WHERE `deviceId`=? AND `insertDatetime` > ? ORDER BY `insertDatetime`  LIMIT ?;"
	}
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	var rows * sql.Rows
	if startDatetime == "" {
		rows, err = stmt.Query(deviceId, count)
	} else {
		rows, err = stmt.Query(deviceId, startDatetime, count)
	}
	if err != nil {
		panic(err.Error())
	}
	var recordList []utility.Accelerometer
	for rows.Next() {
		var record utility.Accelerometer
		rows.Scan(&record.Uuid, &record.DeviceId, &record.AcceRx,  &record.AcceRy,  &record.AcceRz, &record.InsertDatetime)
		recordList = append(recordList, record)
	}
	rows.Close()
	stmt.Close()
	return recordList
}

func GetRecentGyroscopeByDeviceIdAndCount(deviceId string, startDatetime string, count int32) []utility.Gyroscope {
	if count <= 0 {
		count = 100
	}
	var sqlStr string
	if startDatetime == "" {
		sqlStr = "SELECT `uuid`, `deviceId`, `gyroRx`, `gyroRy`, `gyroRz`, `insertDatetime` FROM gyroscope WHERE `deviceId`=? ORDER BY `insertDatetime`  LIMIT ?;"
	} else {
		sqlStr = "SELECT `uuid`, `deviceId`, `gyroRx`, `gyroRy`, `gyroRz`, `insertDatetime` FROM gyroscope WHERE `deviceId`=? AND `insertDatetime` > ? ORDER BY `insertDatetime`  LIMIT ?;"
	}
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	var rows * sql.Rows
	if startDatetime == "" {
		rows, err = stmt.Query(deviceId, count)
	} else {
		rows, err = stmt.Query(deviceId, startDatetime, count)
	}
	if err != nil {
		panic(err.Error())
	}
	var recordList []utility.Gyroscope
	for rows.Next() {
		var record utility.Gyroscope
		rows.Scan(&record.Uuid, &record.DeviceId, &record.GyroRx,  &record.GyroRy,  &record.GyroRz, &record.InsertDatetime)
		recordList = append(recordList, record)
	}
	rows.Close()
	stmt.Close()
	return recordList
}

func GetRecentMagneticByDeviceIdAndCount(deviceId string, startDatetime string, count int32) []utility.Magnetic {
	if count <= 0 {
		count = 100
	}
	var sqlStr string
	if startDatetime == "" {
		sqlStr = "SELECT `uuid`, `deviceId`, `megnX`, `megnY`, `megnZ`, `insertDatetime` FROM magnetic WHERE `deviceId`=? ORDER BY `insertDatetime`  LIMIT ?;"
	} else {
		sqlStr = "SELECT `uuid`, `deviceId`, `megnX`, `megnY`, `megnZ`, `insertDatetime` FROM magnetic WHERE `deviceId`=? AND `insertDatetime` > ? ORDER BY `insertDatetime`  LIMIT ?;"
	}
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	var rows * sql.Rows
	if startDatetime == "" {
		rows, err = stmt.Query(deviceId, count)
	} else {
		rows, err = stmt.Query(deviceId, startDatetime, count)
	}
	if err != nil {
		panic(err.Error())
	}
	var recordList []utility.Magnetic
	for rows.Next() {
		var record utility.Magnetic
		rows.Scan(&record.Uuid, &record.DeviceId, &record.MegnX,  &record.MegnY,  &record.MegnZ, &record.InsertDatetime)
		recordList = append(recordList, record)
	}
	rows.Close()
	stmt.Close()
	return recordList
}

func GetRecentBarometerByDeviceIdAndCount(deviceId string, startDatetime string, count int32) []utility.Barometer {
	if count <= 0 {
		count = 100
	}
	var sqlStr string
	if startDatetime == "" {
		sqlStr = "SELECT `uuid`, `deviceId`, `value`, `insertDatetime` FROM barometer WHERE `deviceId`=? ORDER BY `insertDatetime`  LIMIT ?;"
	} else {
		sqlStr = "SELECT `uuid`, `deviceId`, `value`, `insertDatetime` FROM barometer WHERE `deviceId`=? AND `insertDatetime` > ? ORDER BY `insertDatetime`  LIMIT ?;"
	}
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	var rows * sql.Rows
	if startDatetime == "" {
		rows, err = stmt.Query(deviceId, count)
	} else {
		rows, err = stmt.Query(deviceId, startDatetime, count)
	}
	if err != nil {
		panic(err.Error())
	}
	var recordList []utility.Barometer
	for rows.Next() {
		var record utility.Barometer
		rows.Scan(&record.Uuid, &record.DeviceId, &record.Value, &record.InsertDatetime)
		recordList = append(recordList, record)
	}
	rows.Close()
	stmt.Close()
	return recordList
}

func GetRecentElectricResistanceByDeviceIdAndCount(deviceId string, startDatetime string, count int32) []utility.ElectricResistance {
	if count <= 0 {
		count = 100
	}
	var sqlStr string
	if startDatetime == "" {
		sqlStr = "SELECT `uuid`, `deviceId`, `value`, `insertDatetime` FROM electricResistance WHERE `deviceId`=? ORDER BY `insertDatetime`  LIMIT ?;"
	} else {
		sqlStr = "SELECT `uuid`, `deviceId`, `value`, `insertDatetime` FROM electricResistance WHERE `deviceId`=? AND `insertDatetime` > ? ORDER BY `insertDatetime`  LIMIT ?;"
	}
	stmt, err := DBConn.Prepare(sqlStr)
	if err != nil {
		panic(err.Error())
	}
	var rows * sql.Rows
	if startDatetime == "" {
		rows, err = stmt.Query(deviceId, count)
	} else {
		rows, err = stmt.Query(deviceId, startDatetime, count)
	}
	if err != nil {
		panic(err.Error())
	}
	var recordList []utility.ElectricResistance
	for rows.Next() {
		var record utility.ElectricResistance
		rows.Scan(&record.Uuid, &record.DeviceId, &record.Value, &record.InsertDatetime)
		recordList = append(recordList, record)
	}
	rows.Close()
	stmt.Close()
	return recordList
}